package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.*;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class GraduateProjectCacheDao {
    private static GraduateProjectCacheDao graduateProjectCacheDao = new GraduateProjectCacheDao();


    private GraduateProjectCacheDao() {
    }

    public static GraduateProjectCacheDao getInstance() {
        return graduateProjectCacheDao;
    }

    public static Collection<GraduateProject> findByTeacherId(int teacherId,
                                                              Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的对象
        Collection<GraduateProject> graduateProject = new TreeSet<GraduateProject>();
        //创建查询的主语句
        String sql_select = "SELECT * FROM graduateproject where teacher_id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select);
        //为预编译参数赋值
        pstmt.setInt(1,teacherId);
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            graduateProject.add(new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    null,
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(teacherId),
                    null,
                    null
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProject;
    }
    public static boolean update(GraduateProject graduateProject,Connection connection) throws SQLException {

        //创建sql语句
        String update_sql = "update graduateproject set title=?,graduateProjectCategory_id=?,graduateProjectType_id=?" +
                ",graduateProjectSource_id=?,graduateProjectTrueness_id=?,graduateProjectStatus_id=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setInt(7,graduateProject.getId());
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setInt(2,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(5,graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(6,graduateProject.getGraduateProjectStatus().getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(null,pstmt);
            return false;
        }else
            JdbcHelper.close(null,pstmt);
            return  true;
    }
    public static boolean add(GraduateProject graduateProject,
                              Connection connection) throws SQLException {
        //创建sql语句
        String addGPC_sql =
                "insert into graduateproject (title,graduateProjectCategory_id,graduateProjectType_id, " +
                        "graduateProjectStatus_id,graduateProjectSource_id,graduateProjectTrueness_id,teacher_id) values " +
                        "(?,?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProject.getTitle());
        pstmt.setInt(2,graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3,graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4,graduateProject.getGraduateProjectStatus().getId());
        pstmt.setInt(5,graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6,graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7,graduateProject.getTeacher().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null,pstmt);
            return true;
        } else
            JdbcHelper.close(null,pstmt);
        return false;
    }

    public static boolean delete(int id,Connection connection) throws SQLException{

        //创建sql语句
        String deleteGPC_sql = "DELETE FROM graduateproject WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            JdbcHelper.close(null,pstmt);
            return true;
        }else {
            JdbcHelper.close(null,pstmt);
            return false;
        }
    }
}